from xml.dom.minidom import parse
import openpyxl
from openpyxl.utils import get_column_letter, get_column_interval
import cv2


def excel_read(excel_read_path):
    """
    该函数的作用：解析excel表格文件的结构信息
    :param xml_read_path: 表格文件存放地址
    :return: 四个元素
    All_cell_points：每个单元格在图像中的坐标信息
    #cells_adress：每个单元格的行列信息
    sum(col_list)：图像中表格框的真实宽度
    sum(height_list)：图像中表格框的真实高度
    """
    # 读取excel
    # excel_read_path = "C:\\Users\\14197\\Desktop\\program_donqi\\data\\00015\\00015.xlsx"
    wb = openpyxl.load_workbook(excel_read_path)
    # print(wb.sheetnames)

    # 读取excel文件第一张sheet
    ws = wb[wb.sheetnames[0]]  # worksheet
    # print(ws)

    # 读取sheet表格行列数
    max_row = ws.max_row  # 行
    max_column = ws.max_column  # 列
    # print(ws.max_row)
    # print(ws.max_column)

    # 去除空行
    while True:
        # 行值初始化
        cell_value = None
        for i in range(1, max_column + 1):
            # 对最后一行行，从第一列到对最后一列遍历累计
            cell_value = ws.cell(max_row, i).value or cell_value
        # 查看左下角单元格边界是否有边框
        cell_border = ws.cell(row=max_row, column=1).border.right.border_style
        if cell_value is None and cell_border is None:
            max_row -= 1
        else:
            break

    # 去除空列
    while True:
        # 列值初始化
        cell_value = None
        # 对最后一列，从第一行到对最后一行遍历累计
        for i in range(1, max_row + 1):
            cell_value = ws.cell(i, max_column).value or cell_value
        # 查看右上角单元格边界是否有边框
        cell_border = ws.cell(row=1, column=max_column).border.right.border_style
        if cell_value is None and cell_border is None:
            max_column -= 1
        else:
            break

    # print("行高")
    # 读取行高
    height_list = []
    for i in range(1, max_row + 1):
        height = ws.row_dimensions[i].height
        # 如果为默认行高
        if height is None:
            height = ws.sheet_format.defaultRowHeight
        height_list.append(float(height))
        # print(height)

    # 获取默认列宽
    defaultColWidth = ws.sheet_format.defaultColWidth
    if defaultColWidth is None:
        defaultColWidth = ws.sheet_format.baseColWidth + 1

    # print("列宽")
    # 创建定长空列表
    col_list = [None for i in range(max_column)]
    for i in ws.column_dimensions:
        # 判断该列是否属于表格内的列
        if i in get_column_interval(1, max_column):
            # 获取此列宽下的最大、小列索引
            min = ws.column_dimensions[i].min - 1
            max = ws.column_dimensions[i].max
            col_list[min:max] = [ws.column_dimensions[i].width] * (max - min)
    # 为剩余列赋默认列宽
    for i in range(len(col_list)):
        if col_list[i] is None:
            col_list[i] = defaultColWidth
    # print(col_list)

    # 以左上顶点为原点，各个单元的位置关系,向下、向右为正方向
    # 存放单元格左上、右下顶点坐标与其表格索引
    All_cell_points = []
    for row in range(max_row):
        for column in range(max_column):
            height_end = sum(height_list[:row + 1])
            height_start = height_end - height_list[row]

            width_end = sum(col_list[:column + 1])
            width_start = width_end - col_list[column]

            # 单元格表格索引
            cell_index = get_column_letter(column + 1) + str(row + 1)
            cell_points = [[height_start, width_start], [height_end, width_end], cell_index]
            All_cell_points.append(cell_points)

    # 存放合并单元格的索引
    cell_num = []
    for merged_cell in ws.merged_cells.ranges:
        # 获取合并单元格的左上、右下单元格的表格索引
        left_col, left_row, right_col, right_row = merged_cell.bounds
        # 计算左上、右下单元格的坐标列表索引
        left_cell_num = (left_row - 1) * max_column + left_col - 1
        right_cell_num = (right_row - 1) * max_column + right_col - 1
        # 保留合并单元格左上右下角点坐标
        All_cell_points[left_cell_num][1] = All_cell_points[right_cell_num][1]

        # 记录被合并的单元格的表格索引
        for i in range(len(merged_cell.left)):
            for j in range(len(merged_cell.top)):
                cell_num.append(left_cell_num + i * max_column + j)
        # 左上角单元格保存合并单元格的位置信息所以保留，去除起始单元格索引
        cell_num.remove(left_cell_num)
        # cell_num.append([left_cell_num, right_cell_num])

    # 将要删除的单元格索引降序排列
    cell_num.sort(reverse=True)
    # 从后往前删除 单元格坐标列表 中  合并单元格坐标
    for i in range(len(cell_num)):
        del All_cell_points[cell_num[i]]

    # print(All_cell_points)

    '''
    # 将合并后的单元格角点可视化
    img_origin = cv2.imread("C:\\Users\\14197\\Desktop\\program_donqi\\data\\0006\\0006_jiaozheng.jpg")
    size = img_origin.shape
    # print(size)
    y = size[0]  # 图片垂直尺寸高y
    x = size[1]  # 图片水平尺寸宽x
    for i in range(len(All_cell_points)):
        cv2.circle(img_origin, (
            int(All_cell_points[i][0][1] / sum(col_list) * x), int(All_cell_points[i][0][0] / sum(height_list) * y)), 4,
                   (255, 0, 0), 2)
        cv2.circle(img_origin, (
            int(All_cell_points[i][1][1] / sum(col_list) * x), int(All_cell_points[i][1][0] / sum(height_list) * y)), 4,
                   (255, 0, 0), 2)
    # img_origin = cv2.resize(img_origin, (int(x / 3), int(y / 3)))
    cv2.imshow("img_origin", img_origin)
    cv2.waitKey(0)
    cv2.destroyAllWindows()
    '''
    return All_cell_points, sum(col_list), sum(height_list)


def xml_read(xml_read_path):
    """
    该函数的作用：解析excel表格文件的结构信息
    :param xml_read_path: 表格文件结构信息文件XMl文件存放地址
    :return: 四个元素
    All_cell_points：每个单元格在图像中的坐标信息
    cells_adress：每个单元格的行列信息
    sum(col_list)：图像中表格框的真实宽度
    sum(height_list)：图像中表格框的真实高度
    """
    # XML文件的读取
    dom = parse(xml_read_path)
    # 获取文档元素对象
    data = dom.documentElement
    # 读取sheet
    sheet = data.getElementsByTagName('Worksheet')
    # 读取 table
    table = sheet[0].getElementsByTagName('Table')
    # 读取table中的默认参数
    # 默认行高
    de_height = float(table[0].getAttribute("ss:DefaultRowHeight"))
    # 默认列宽
    print(table[0].getAttribute("ss:DefaultColumnWidth"))
    de_width = float(table[0].getAttribute("ss:DefaultColumnWidth"))

    # 默认行数
    row_num = int(table[0].getAttribute("ss:ExpandedRowCount"))
    # 默认列数
    col_num = int(table[0].getAttribute("ss:ExpandedColumnCount"))
    print("行数：", row_num, "列数：", col_num)
    # 读取列宽
    # Column = table[0].getElementsByTagName('Column')
    # 读取列宽
    # 首先读取所有的列宽元素
    Column = table[0].getElementsByTagName('Column')
    col_list = col_num * [de_width]
    # 列宽元素的索引
    i = 0
    for col in Column:
        if col.getAttribute("ss:Span") != '' and col.getAttribute("ss:Index") != '':
            index = int(col.getAttribute("ss:Index"))
            span = int(col.getAttribute("ss:Span"))
            width = float(col.getAttribute("ss:Width"))
            col_list[index - 1:index + span] = [width] * (span + 1)
            i = index + span
        elif col.getAttribute("ss:Span") == '' and col.getAttribute("ss:Index") != '':
            index = int(col.getAttribute("ss:Index"))
            width = float(col.getAttribute("ss:Width"))
            col_list[index - 1] = width
            i = index
        elif col.getAttribute("ss:Span") == '' and col.getAttribute("ss:Index") == '':
            width = float(col.getAttribute("ss:Width"))
            col_list[i] = width
            i = i + 1
            # print(width, i)

    # 读取所有的行高信息
    Rows = table[0].getElementsByTagName('Row')
    # 读取每一行行高
    height_list = []
    for row in Rows:
        # height_list.append(float(row.getAttribute("ss:Height")))
        if row.getAttribute("ss:Height") == '':
            height_list.append(float(de_height))
        else:
            height_list.append(float(row.getAttribute("ss:Height")))

    # 以左上顶点为原点，各个单元的位置关系,向下、向右为正方向
    All_cell_points = []
    # 坐标的行列信息【【cell_col, cell_row】, ...】,为后续的表格写入做准备
    cells_adress = []
    cell_col = []
    cell_row = []
    # 同时获取表格的结构信息
    for index in range(len(Rows)):
        # 获取每一行中的单元格并计算
        cells = Rows[index].getElementsByTagName('Cell')
        #     print()
        #     print(index+1,'行：')

        # i : cell索引
        i = 0
        cell_col = []
        cell_row = []
        for index_cell, cell in enumerate(cells):
            # print('每行的单元格的次序：',index_cell+1)
            # print()
            # 计算得到单元格的起始单元格位置
            # 计算列宽
            #  ss:Index 一般是合并单元格从第一个开始就会出现ss:Index或者是某个纵向合并单元格之后多行的合并单元格
            # ，其后面的数字是在原本一个的基础上又合并的单元格数量

            # 这是该行的第一个单元格不是从最边上开始的且不是合并单元格
            if cell.getAttribute("ss:Index") and cell.getAttribute('ss:MergeAcross') == '':
                # print("ss:Index:")
                cell_col = []
                cell_row = []
                i = int(cell.getAttribute("ss:Index")) - 1
                cell_col.append(i)
                width = col_list[i]
                width_start = sum(col_list[:i])
                width_end = width + width_start

                cell_width = [width_start, width_end]
                # print('width:',cell_width)
                i = i + 1
                cell_col.append(i)

            # 合并单元格的数量和方向
            # MergeAcross横向合并，其后面的数字是在原来一个的基础上合并了单元格的数量
            # #这是该行的第一个单元格不是从最边上开始的而且是合并单元格
            elif cell.getAttribute("ss:Index") and cell.getAttribute('ss:MergeAcross'):
                # print("ss:Index:ss::Merge")
                cell_col = []
                cell_row = []
                i = i + int(cell.getAttribute("ss:Index")) - 1
                merge_col = int(cell.getAttribute("ss:MergeAcross"))
                cell_col.append(i)
                width = sum(col_list[i:i + merge_col + 1])
                width_start = sum(col_list[:i])
                width_end = width + width_start
                cell_width = [width_start, width_end]
                # print('width:',cell_width)

                cell_col.append(i)
                i = i + merge_col + 1
                cell_col.append(i)
            # 按照正常顺序合并的单元格
            elif cell.getAttribute("ss:MergeAcross"):
                # print("ss:MergeAcross:")
                cell_col = []
                cell_row = []
                merge_col = int(cell.getAttribute("ss:MergeAcross"))
                # 存放每个单元格起始和结束的位置
                width = sum(col_list[i:i + merge_col + 1])
                width_start = sum(col_list[:i])
                width_end = width + width_start
                cell_width = [width_start, width_end]

                # print('width:',cell_width)

                cell_col.append(i)
                i = i + merge_col + 1
                cell_col.append(i)



            # 正常的单元格
            elif cell.getAttribute("ss:Index") == '' and cell.getAttribute("ss:MergeAcross") == '':
                # print("ss:IN ss:m ==   ")
                cell_col = []
                cell_row = []

                # print(i)

                width = col_list[i]
                cell_col.append(i)
                width_start = sum(col_list[:i])
                width_end = width + width_start
                cell_width = [width_start, width_end]

                # print('width:',cell_width)

                i = i + 1
                cell_col.append(i)

            # 计算单元格的行高
            if cell.getAttribute("ss:MergeDown"):
                merge_row = int(cell.getAttribute("ss:MergeDown"))
                height = sum(height_list[index:index + merge_row + 1])
                height_start = sum(height_list[:index])
                height_end = height_start + height
                cell_height = [height_start, height_end]

                # print('height:',cell_height)

                cell_row.append(index)
                cell_row.append(index + merge_row + 1)

            elif cell.getAttribute("ss:MergeDown") == '':
                height_start = sum(height_list[:index])
                height = height_list[index]
                height_end = height_start + height
                cell_height = [height_start, height_end]

                #  print('height:',cell_height)

                cell_row.append(index)
                cell_row.append(index + 1)

            # 存放单元格左上和右下角点的坐标
            cell_points = [[height_start, width_start], [height_end, width_end]]
            All_cell_points.append(cell_points)
            # 存放每个单元格的行列位置信息
            cells_adress.append([cell_col, cell_row])

    return All_cell_points, cells_adress, sum(col_list), sum(height_list)
